package com.xy.breakfast.mapper;

import com.xy.breakfast.entity.Clients;
import com.xy.breakfast.entity.Orders;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

//订单数据封装
@Mapper
public interface OrdersMapper extends BaseMapper<Orders>{

    @Select("SELECT * FROM orders WHERE id = #{id}")
    @Results({
            @Result(property = "clientsId", column = "clients_id", jdbcType = JdbcType.INTEGER),
            @Result(property = "foodsId", column = "foods_id", jdbcType = JdbcType.INTEGER),
            @Result(property = "createTime", column = "create_time", jdbcType = JdbcType.INTEGER),
            @Result(property = "takeTime", column = "take_time", jdbcType = JdbcType.INTEGER),
            @Result(property = "isPack", column = "is_pack", jdbcType = JdbcType.INTEGER),
            @Result(property = "payMethod", column = "pay_method", jdbcType = JdbcType.INTEGER)

    })
    public Orders getById(int id);

    @Override
    @Insert("INSERT INTO orders (clients_id,foods_id,num, price,create_time,take_time, is_pack,phone,address,pay_method) " +
            "VALUES (#{clientsId},#{foodsId},#{num}, #{price},#{createTime}, #{takeTime}, #{isPack},#{phone},#{address}, #{payMethod})")
    @Options(useGeneratedKeys = true, keyProperty = "id") //设置自增主键
    public void save(Orders o);

    //查询客户的订单
    @Select("select * from orders where clients_id=#{clientsId}")
    @Results({
            @Result(property = "clientsId", column = "clients_id", jdbcType = JdbcType.INTEGER),
            @Result(property = "foodsId", column = "foods_id", jdbcType = JdbcType.INTEGER),
            @Result(property = "createTime", column = "create_time", jdbcType = JdbcType.INTEGER),
            @Result(property = "takeTime", column = "take_time", jdbcType = JdbcType.INTEGER),
            @Result(property = "isPack", column = "is_pack", jdbcType = JdbcType.INTEGER),
            @Result(property = "payMethod", column = "pay_method", jdbcType = JdbcType.INTEGER)

    })
    public List<Orders> getByClientsId(int clientsId);

    //统计每个小时订单的数量
    @Select("select HOUR(create_time) as x, count(id) as y\n" +
            "            from orders\n" +
            "            where day(create_time) = day(now())\n" +
            "            group by HOUR(create_time)\n" +
            "            order by HOUR(create_time) asc")
    public List<Map<String,String>> getOrdersNumGroupByHour();


//SELECT
//    foods.types_id AS '类型',
//    DAY(orders.create_time) AS '几号',
//    COUNT(orders.id) AS '数量',
//    SUM(COUNT(orders.id)) OVER (PARTITION BY DAY(orders.create_time)) AS '同一天总数'
//FROM
//    orders
//        INNER JOIN foods ON orders.foods_id = foods.id
//GROUP BY
//    foods.types_id,
//    DAY(orders.create_time);

    //x:同一天的不同类型  y:同一类型的不同日期 k:同一天的同类型 h:分不同一天的各自的总数
    @Select("SELECT\n" +
            "    foods.types_id AS x,\n" +
            "    DAY(orders.create_time) AS y,\n" +
            "    COUNT(orders.id) AS k,\n" +
            "    SUM(COUNT(orders.id)) OVER (PARTITION BY DAY(orders.create_time)) AS h\n" +
            "FROM\n" +
            "    orders\n" +
            "        INNER JOIN foods ON orders.foods_id = foods.id\n" +
            "GROUP BY\n" +
            "    foods.types_id,\n" +
            "    DAY(orders.create_time);")
    public List<Map<String,Object>> getOrdersByTypesIdDay();

    //x:'小时' y:'是否打包',k:'数量'
    @Select("select HOUR(orders.create_time) as x,is_pack as y,count(is_pack) as k\n" +
            "    from orders,foods\n" +
            "    where orders.foods_id=foods.id\n" +
            "    group by HOUR(create_time),is_pack\n" +
            "    order by HOUR(orders.create_time) asc ;")
    public List<Map<String,Object>> getOrdersByTypesIdHour();

//    不同类型销售比例图
    @Select("SELECT  SUM(o.num) AS value,f.name AS name\n" +
            "FROM orders o\n" +
            "         JOIN foods f ON o.foods_id = f.id\n" +
            "         JOIN types t ON f.types_id = t.id\n" +
            "where day(o.create_time)=day(now())\n" +
            "GROUP BY f.types_id, f.name;")
    public List<Map<String,Object>> getOrdersByNum();

    @Override
    public void deleteById(int id);

    public void update(Orders o);

    public List<Orders> getAll();

    public List<Orders> getPage(int start,int end);
}
